package cn.jb698.codec.msm.generator.v5base;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONObject;

import cn.jb698.codec.msm.generator.util.CodecCfgObjUtils;
import cn.jb698.codec.msm.generator.util.CodecDbConnectionUtils;
import cn.jb698.codec.msm.generator.util.CodecGeneUtils;
import cn.jb698.codec.msm.generator.v5base.beans.BcrudCfgColumnObj;
import cn.jb698.codec.msm.generator.v5base.beans.BcrudCfgTableObj;
import cn.jb698.codec.msm.generator.v5base.beans.BcrudConfigBean;
import cn.jb698.codec.msm.generator.v5base.req.BcrudCreateJsonCreateTableJsonBeanReq;

/**
 * 配置对象生成-Oracle的实现
 * 
 * @author SDPC_LIU
 *
 */
public class BcrudCfgObjCreateServiceOracleImpl implements BcrudCfgObjCreateService {

	private static final Logger log = LoggerFactory.getLogger(BcrudCfgObjCreateServiceOracleImpl.class);

	private Connection generateConnection(BcrudConfigBean bcrudConfigBean) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
		String dbDriverClassName = bcrudConfigBean.getDbDriverClassName();
		String dbConnectionUrl = bcrudConfigBean.getDbConnectionUrl();
		String dbUserName = bcrudConfigBean.getDbUserName();
		String dbPassword = bcrudConfigBean.getDbPassword();
		Class.forName(dbDriverClassName).newInstance();
		Connection currConnection = DriverManager.getConnection(dbConnectionUrl, dbUserName, dbPassword);
		return currConnection;
	}

	/**
	 * 
	 * @param qrst
	 * @param tableCfg
	 * @return
	 */
	private List<BcrudCfgColumnObj> doAllCfgColumnObjListCreate(ResultSet qrst, BcrudCfgTableObj tableCfg) {
		List<BcrudCfgColumnObj> rstList = new ArrayList<>();
		try {
			while (qrst.next()) {
//                String table_name = qrst.getString("TABLE_NAME");
				String column_name = qrst.getString("COLUMN_NAME");
//                column_name = column_name.toUpperCase(); // Oracle字段名称全部转大写 
				String data_type = qrst.getString("DATA_TYPE"); // NUMBER , VARCHAR2 , LONG , TIMESTAMP
				Long data_length = qrst.getLong("DATA_LENGTH"); // 数据长度
				Integer data_precision = qrst.getInt("DATA_PRECISION"); // 数据范围长度
				Integer data_scale = qrst.getInt("DATA_SCALE"); // 数据精度,大于0则认为Double类型,小于0或空则认为是Integer或Long类型
				String comments = qrst.getString("COMMENTS"); //

				String propName = CodecGeneUtils.parseColumnNameToPropName(column_name.toLowerCase()); // 属性名称 .
				Integer columnType = null;
				if ("NUMBER".equals(data_type)) { // 数字类型
					Integer type_int_maxlength = 10; // int数据最大长度
					boolean isDecimal = data_precision != null && data_precision > 0 && data_scale != null && data_scale > 0;
					boolean isBigint = data_precision != null && data_precision > 0 && data_precision > type_int_maxlength && (data_scale == null || data_scale == 0);
					boolean isInteger = data_precision != null && data_precision > 0 && data_precision <= type_int_maxlength && (data_scale == null || data_scale == 0);
					
					if (isDecimal) { // Double类型
						columnType = Types.DECIMAL;
					} else if (isBigint) { // Long 类型
						columnType = Types.BIGINT;
					} else if (isInteger) { // INT 类型
						columnType = Types.INTEGER;
					} else {
						// 默认DECIMAL
						columnType = Types.DECIMAL;
					}
				} else if (data_type.startsWith("TIMESTAMP") || "TIMESTAMP".equals(data_type) || "DATE".equals(data_type)) { // 日期
					if("DATE".equals(data_type)) {
						columnType = Types.DATE;
					}else {
						columnType = Types.TIME;
					}
				} else {
					// 默认为字符串类型
					columnType = Types.VARCHAR;
				}

				BcrudCfgColumnObj columnJsBean = new BcrudCfgColumnObj();
				
				// 根据字段类型计算补充属性信息,
				CodecCfgObjUtils.doColumnTypeCalcAndFill(columnJsBean, columnType, tableCfg);
				
				CodecCfgObjUtils.doColumnTypeExpressCalcAndFill(columnJsBean,columnJsBean.getColumnType(), null, null); 

				columnJsBean.setColumn(column_name); // 字段名称
				columnJsBean.setColumnLength(data_length); // 数据长度
				columnJsBean.setPropName(propName); // 属性名称
				columnJsBean.setComment(comments); // 字段备注 .
				rstList.add(columnJsBean);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rstList;

	}

	/**
	 * 生成表的JSONBean
	 * @param req  请求对象
	 * @param srcCfgTableBean 表配置
	 * 
	 * @return
	 * @throws SQLException
	 */
	public BcrudCfgTableObj createTableJsonBean(BcrudCreateJsonCreateTableJsonBeanReq req, BcrudCfgTableObj srcCfgTableBean) throws SQLException {

		String basePackage = req.getBasePackage();
//		String ctrlerPackage = req.getProjPackage();
		Connection connection = req.getConnection();
		String[] primKeyColumns = req.getPrimKeyColumns();
		String tableName = req.getTableName();
		String[] ignoreColumns = req.getIgnoreColumns();
		String tableAlias = req.getTableAlias();
		String modelJavaTypeName = req.getModelJavaTypeName();
		boolean hasAutoCutLengh = req.isHasAutoCutLengh();
		boolean hasPersistence = req.isHasPersistence();
		boolean hasBaomidou = req.isHasBaomidou();
		boolean hasSwaggerUi = req.isHasSwaggerUi();

		BcrudCfgTableObj rtTableJsBean = new BcrudCfgTableObj();
		if(srcCfgTableBean != null ) {
			rtTableJsBean = srcCfgTableBean;
		}

		if (basePackage == null) {
			basePackage = "com.jianbin698.demoproject";
//			throw new IllegalArgumentException("基础包不能为空");
		}
		
		String dbDialect = CodecDbConnectionUtils.getDialectByConnection(connection); // 数据库类型, mysql,oracle,sqlserver 等
//		DatabaseMetaData databaseMetaData = connection.getMetaData();
		List<String> pkColumnNameList = new ArrayList<String>();
		String comment = null; // 表级别字段注释
		
		// 提取主键
		if (primKeyColumns != null && primKeyColumns.length > 0) {
			// 有指定主键字段,则按指定的主键字段来生成
			for (String pkColumn : primKeyColumns) {
				pkColumnNameList.add(pkColumn);
			}
		} else {
			// 否则从表里读取主键信息
			// Oracle获取表主键方法
			String prepStmt = "SELECT  CU.TABLE_NAME ,CU.COLUMN_NAME ,CU.CONSTRAINT_NAME ,AU.CONSTRAINT_NAME AS AU_CONSTRAINT_NAME ,AU.CONSTRAINT_TYPE "
					+ "FROM  USER_CONS_COLUMNS CU ,USER_CONSTRAINTS AU WHERE AU.CONSTRAINT_TYPE = 'P' AND AU.CONSTRAINT_NAME = CU.CONSTRAINT_NAME AND CU.TABLE_NAME = ? ";
			PreparedStatement prepareStatement = connection.prepareStatement(prepStmt);
			prepareStatement.setString(1, tableName);
			ResultSet qrst = prepareStatement.executeQuery();
			while (qrst.next()) {
				String pk_columnName = qrst.getString("COLUMN_NAME");
//                    pk_columnName = pk_columnName.toUpperCase(); // ORACLE 字段转大写 
				pkColumnNameList.add(pk_columnName);
			}
		}
		log.debug("解析后的主键名称列表:" + pkColumnNameList);
		
		// 提取表注释
		String createTableDdl = null ;

		// 提取字段及数据结构(后期考虑从DDL里读取)
		List<BcrudCfgColumnObj> allColumnsList = null; // 所有字段及注释
		
		{
			// ORACLE 取字段数据类型实现
			String prepStmt = "SELECT T.TABLE_NAME,T.COLUMN_NAME,T.DATA_TYPE,T.DATA_LENGTH ,T.DATA_PRECISION, T.DATA_SCALE ,C.COMMENTS "
					+ "FROM  USER_TAB_COLUMNS T LEFT JOIN USER_COL_COMMENTS C ON T.TABLE_NAME = C.TABLE_NAME AND T.COLUMN_NAME = C.COLUMN_NAME " + " WHERE T.TABLE_NAME= ? ";
			PreparedStatement prepareStatement = connection.prepareStatement(prepStmt);
			prepareStatement.setString(1, tableName);
			ResultSet qrst = prepareStatement.executeQuery();
			allColumnsList = doAllCfgColumnObjListCreate(qrst, rtTableJsBean);
		}

		// 提取自增字段
		List<String> autoIncrementColumnNameList = new ArrayList<>();
		Map<String,Object> autoIncrementColumnNameMap = new HashMap<>(pkColumnNameList.size());
		if(!autoIncrementColumnNameList.isEmpty()) {
			for (String tmpColumnName : autoIncrementColumnNameList) {
				autoIncrementColumnNameMap.put(tmpColumnName, tmpColumnName);
			}
		}
		
		log.debug("取的所有字段映射信息:" + allColumnsList);
		
		// PK字段列表
		List<BcrudCfgColumnObj> pkColumnsList = new ArrayList<BcrudCfgColumnObj>();
		
		// 其它字段列表
		List<BcrudCfgColumnObj> otherColumnsList = new ArrayList<BcrudCfgColumnObj>();
		
		Map<String, Boolean> pkColumnNameMap = new HashMap<String, Boolean>(16);
		for (String pkColumnName : pkColumnNameList) {
			pkColumnNameMap.put(pkColumnName, true);
		}
		
		for (BcrudCfgColumnObj tmpColumn : allColumnsList) {
			String tmpColumnName = tmpColumn.getColumn();
			Boolean isPk = pkColumnNameMap.get(tmpColumnName);
			if (isPk != null && isPk) {
				pkColumnsList.add(tmpColumn);
				Object tmp_autoIncrementColumn = autoIncrementColumnNameMap.get(tmpColumnName);
				if(tmp_autoIncrementColumn != null ) {
					tmpColumn.setIsAutoIncrement(true);
				}
				tmpColumn.setIsPrimKey(isPk);
			} else {
				otherColumnsList.add(tmpColumn);
			}
		}
		
		if (ignoreColumns != null && ignoreColumns.length > 0) {
			Map<String, String> ignoreColumnsMap = new HashMap<>(ignoreColumns.length);
			for (String ignoreColumn : ignoreColumns) {
				ignoreColumnsMap.put(ignoreColumn, ignoreColumn);
			}
			
			List<BcrudCfgColumnObj> tgtOtherColumnList = new ArrayList<>();
			for (BcrudCfgColumnObj tmp_columnJsBean : otherColumnsList) {
				String tmp_column = tmp_columnJsBean.getColumn();
				String tmp_ignoreColumn = ignoreColumnsMap.get(tmp_column);
				if (tmp_ignoreColumn != null && !"".equals(tmp_ignoreColumn)) {
					continue;
				}
				tgtOtherColumnList.add(tmp_columnJsBean);
			}
			otherColumnsList = tgtOtherColumnList;
		}
		
		// 表别名:
		if (tableAlias != null) {
			rtTableJsBean.setTableAlias(tableAlias);
		}
		rtTableJsBean.setDbType(dbDialect);
		rtTableJsBean.setProjPackage(srcCfgTableBean.getProjPackage());
		rtTableJsBean.setBasePackage(basePackage);
		rtTableJsBean.setTableName(tableName);
		rtTableJsBean.setPkColumns(pkColumnsList);
		rtTableJsBean.setCreateTableDdl(createTableDdl);
		rtTableJsBean.setComment(comment);
		rtTableJsBean.setOtherColumns(otherColumnsList);
		if (modelJavaTypeName != null) {
		    rtTableJsBean.getFileObjEntity().setObjName(modelJavaTypeName);
		}
		rtTableJsBean.setHasSwaggerUi(hasSwaggerUi);
		rtTableJsBean.setHasPersistence(hasPersistence);
		rtTableJsBean.setHasBaomidou(hasBaomidou);
		rtTableJsBean.setHasAutoCutLengh(hasAutoCutLengh);
		return rtTableJsBean;
	}

	/**
	 * 创建表JsBean
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	@Override
	public List<BcrudCfgTableObj> doBcrudCfgTableObjListCreate(BcrudConfigBean bcrudConfigBean) throws Exception {
		log.debug("开始创建 TableJsBean 列表 ");
		List<BcrudCfgTableObj> rtList = new ArrayList<>();

		String basePackageName = bcrudConfigBean.getBasePackageName(); // 基础包名.
		String projPackageName = bcrudConfigBean.getProjPackageName(); // 项目包名
		List<BcrudCfgTableObj> lmtDataTables = bcrudConfigBean.getCfgTables(); // 配置限制的表.
		Connection currConnection = generateConnection(bcrudConfigBean);
		String databaseName = CodecDbConnectionUtils.getDatabaseName(currConnection);
		log.debug("数据库名称:" + databaseName);
		List<String> allTableNameList = CodecDbConnectionUtils.getAllTablesNameList(currConnection);
		log.debug("所有表的名称:" + allTableNameList);

		for (String tableName : allTableNameList) {
			BcrudCfgTableObj currCfgTableBean = null;
			if (tableName != null) {
				Boolean isCreate = false; // 默认不创建.
				if (lmtDataTables != null && lmtDataTables.size() > 0) {
					for (BcrudCfgTableObj tmpCfgTableBean : lmtDataTables) {
						String lmtTableName = tmpCfgTableBean.getTableName();
						if (lmtTableName != null && lmtTableName.equals(tableName)) {
							currCfgTableBean = tmpCfgTableBean;
							isCreate = true;
							break;
						}
					}
				} else {
					isCreate = true;
				}
				if (!isCreate) { // 不创建,则继续下一表.
					continue;
				}

				String tableAlias = "t";
				String modelJavaTypeName = null;
				String[] primKeyColumns = null;
				if (currCfgTableBean != null && currCfgTableBean.getTableAlias() != null) {
					primKeyColumns = currCfgTableBean.getPrimKeyColumns();
					modelJavaTypeName = currCfgTableBean.getDefEntityObjName();
					tableAlias = currCfgTableBean.getTableAlias();
				}

				log.debug("开始生成表(" + tableName + ")的JsonMap:");
				String[] ignoreColumns = currCfgTableBean.getIgnoreColumns();
				boolean hasSwaggerUi = currCfgTableBean.getHasSwaggerUi();
				boolean hasPersistence = currCfgTableBean.getHasPersistence();
				boolean hasAutoCutLengh = currCfgTableBean.getHasAutoCutLengh();
				boolean hasBaomidou = currCfgTableBean.getHasBaomidou();
				BcrudCreateJsonCreateTableJsonBeanReq creq = new BcrudCreateJsonCreateTableJsonBeanReq();
				creq.setConnection(currConnection);
				creq.setTableName(tableName);
				creq.setBasePackage(basePackageName);
				creq.setProjPackage(projPackageName);
				creq.setTableAlias(tableAlias);
				creq.setModelJavaTypeName(modelJavaTypeName);
				creq.setPrimKeyColumns(primKeyColumns);
				creq.setIgnoreColumns(ignoreColumns);
				creq.setHasAutoCutLengh(hasAutoCutLengh);
				creq.setHasPersistence(hasPersistence);
				creq.setHasBaomidou(hasBaomidou);
				creq.setHasSwaggerUi(hasSwaggerUi);
				BcrudCfgTableObj tableJsBean = createTableJsonBean(creq, currCfgTableBean );
				log.debug("生成后的Bean信息(JSON)为:" + JSONObject.toJSONString(tableJsBean));
				rtList.add(tableJsBean);
			}
		}
		return rtList;
	}


//	/**
//	 * 创建JSON文件到输出目录
//	 * 
//	 * @param bcrudConfigBean
//	 * @param tableJsBeans
//	 * @return
//	 * @throws IOException
//	 */
//	@Override
//	public List<String> doCfgTableObjListOutputToJsonFiles(BcrudConfigBean bcrudConfigBean, List<BcrudCfgTableObj> tableJsBeans) throws IOException {
//		List<String> filePathList = new ArrayList<>();
//
//		if (tableJsBeans == null || tableJsBeans.size() < 1) {
//			return filePathList;
//		}
//		String jsonFileOutDir = bcrudConfigBean.getJsonFileOutDir();
//		File jsonFileOutDirFile = new File(jsonFileOutDir);
//		if (!jsonFileOutDirFile.exists()) {
//			jsonFileOutDirFile.mkdirs();
//		}
//		for (BcrudCfgTableObj tableJsonMap : tableJsBeans) {
//			String jsonFileName = tableJsonMap.getTableName() + ".json";
//			log.debug("生成后的json文件名为:" + jsonFileName);
//			String jsonStr = JSONObject.toJSONString(tableJsonMap, true);
//			String outFilePath = jsonFileOutDir + jsonFileName;
//			FileWriter fwriter = new FileWriter(outFilePath);
//			fwriter.write(jsonStr);
//			fwriter.flush();
//			fwriter.close();
//			filePathList.add(outFilePath);
//		}
//		return filePathList;
//	}

}
